# -*- coding: utf8 -*-

# 通过文档和指定数据库的架构生成数据库脚本。

from enum import unique
from .utils import flat_append, strip_str
from .commdef import DataColumn, DataTable
from psycopg2 import connect
import sys

from typing import Dict, List, Iterable, Sequence, Tuple, Union, Any
from openpyxl import load_workbook, Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell.cell import Cell, TYPE_STRING, TYPE_FORMULA, TYPE_NUMERIC, TYPE_BOOL, TYPE_NULL, TYPE_INLINE, TYPE_ERROR, TYPE_FORMULA_CACHE_STRING


def _read_str(
    cell: Cell,
    dv: str = ''
) -> str:
    if cell.data_type == TYPE_NUMERIC:
        return str(int(cell.value)) if cell.value is not None else dv
    elif cell.data_type == TYPE_STRING:
        return str(cell.value)
    else:
        return dv


def _read_float(
    cell: Cell,
    dv: float = 0
) -> float:
    if cell.data_type == TYPE_NUMERIC:
        return float(cell.value) if cell.value is not None else dv
    elif cell.data_type == TYPE_STRING:
        try:
            return float(strip_str(cell.value))
        except ValueError:
            return dv
    else:
        return dv


def _read_int(
    cell: Cell,
    dv: int = 0
) -> int:
    if cell.data_type == TYPE_NUMERIC:
        return int(cell.value) if cell.value is not None else dv
    elif cell.data_type == TYPE_STRING:
        try:
            return int(strip_str(cell.value))
        except ValueError:
            return dv
    else:
        return dv


def _read_bool(
    cell: Cell,
    dv: bool = False
) -> bool:
    if cell.data_type == TYPE_NUMERIC or cell.data_type == TYPE_BOOL:
        return bool(cell.value) if cell.value is not None else dv
    elif cell.data_type == TYPE_STRING:
        s = strip_str(cell.value).lower()
        return s == '是' or s == 'yes' or s == 'y'
    else:
        return dv


def _load_tables_from_doc(
    doc_path: str,
    verbose: bool
) -> List[DataTable]:
    r''' 从文档中加载所有的数据库表。

    :param doc_path: 文档路径。
    :param verbose: 是否显示加载过程。

    :return: 已加载的所有数据库表。
    '''
    wb: Workbook = load_workbook(doc_path, read_only=True, data_only=True)
    try:
        # 加载所有的表名。
        all_table_names = []
        index_sheet: Worksheet = wb.get_sheet_by_name('目录')
        for row in index_sheet.iter_rows(min_row=2):  # 跳过标题行。
            table_name, table_comment, *_ = row
            table_name = strip_str(table_name.value)
            if not table_name:
                continue

            all_table_names.append((table_name, str(table_comment.value) if table_comment.value is not None else ''))

        if verbose:
            print('Found {:d} tables'.format(len(all_table_names)))

        tables: List[DataTable] = []
        for table_name, table_comment in all_table_names:
            if verbose:
                print('Process {!r:} ...'.format(table_name))

            try:
                sheet: Worksheet = wb.get_sheet_by_name(table_name)
            except Exception as exc:
                print(exc, file=sys.stderr)
                continue

            cols = []
            for row in sheet.iter_rows(min_row=2):  # 跳过标题行。
                (col_name, col_type, col_size, col_scale, col_comment, col_nullable, col_default, col_unique,
                    col_check, col_primary_key, col_foreign_key, col_index, *_) = row

                col_name = _read_str(col_name)
                if not col_name:
                    continue

                cols.append(DataColumn(
                    col_name,
                    _read_str(col_type, 'varchar'),
                    _read_int(col_size),
                    _read_int(col_scale),
                    _read_str(col_comment),
                    _read_bool(col_nullable),
                    _read_str(col_default),
                    _read_str(col_unique),
                    _read_str(col_check),
                    _read_bool(col_primary_key),
                    _read_str(col_foreign_key),
                    _read_str(col_index)
                ))

            tables.append(DataTable(name=table_name, comment=table_comment, cols=cols))

        return tables
    finally:
        wb.close()


def _sql_type(
    t: str,
    *,
    size: int = 0,
    scale: int = 0
) -> str:
    r''' 将指定的类型转化为SQL命令中的类型。

    :param t: 数据类型。
    :param size: 长度。
    :param scale: 数值比例。
    '''
    if t == 'varchar' and size > 0:
        return 'varchar({})'.format(size)
    elif t == 'numeric':
        if size > 0:
            if scale > 0:
                return 'numeric({:d}, {:d})'.format(size, scale)
            else:
                return 'numeric({:d})'.format(size)
        else:
            return 'numeric'
    elif t == 'int':
        return 'integer'
    else:
        return t


def _literal(
    v: Any,
    t: str = 'varchar'
) -> str:
    r''' 对指定的值进行编码和转义，转化为SQL命令中的常量。

    :param v: 原始值。
    :param t: 数据类型。
    '''
    if t == 'varchar':
        return repr(str(v))
    elif t == 'integer':
        return repr(int(float(v)))
    elif t == 'numeric':
        return repr(float(v))
    else:
        return repr(v)


def _dump_sql(
    base_tables: Sequence[DataTable],
    new_tables: Sequence[DataTable],
    drop_cascade,
    file=sys.stdout
) -> None:
    def _write(
        s: str = '',
        *args: Any
    ) -> None:
        if args:
            print(s.format(*args), end='', file=file)
        else:
            print(s, end='', file=file)

    def _writeline(
        s: str = '',
        *args: Any
    ) -> None:
        if args:
            print(s.format(*args), file=file)
        else:
            print(s, file=file)

    if new_tables:
        _writeline('-- DROP OLD TABLES --')
    
    for table in reversed(new_tables):
        if(drop_cascade):
            _writeline('DROP TABLE IF EXISTS {} CASCADE;', table.name)
        else:
            _writeline('DROP TABLE IF EXISTS {};', table.name)
    if new_tables:
        _writeline('-- CREATE NEW TABLES --')
    for table in new_tables:
        table_name = table.name.lower()
        _writeline('-- {} --', table_name)
        _writeline('CREATE TABLE {} (', table_name)
        pk = []
        uniques = {}
        indexes = {}
        for ci, col in enumerate(table.cols):
            col_name = col.col_name.lower().strip()
            col_unique = col.col_unique.lower().strip()
            col_index = col.col_index.lower().strip()
            _write('\t{} {}', col_name, _sql_type(col.col_type, size=col.col_size, scale=col.col_scale))
            if not col.col_nullable:
                _write(' NOT NULL')
            if col_unique:
                flat_append(uniques, col_unique, col_name)
            if col.col_check:
                cck_list = col.col_check.split(',')
                _write(' CHECK({})', ' AND '.join(col_name + cck_item for cck_item in cck_list if cck_item))
            if col.col_default:
                _write(' DEFAULT {}', _literal(col.col_default, col.col_type))
            if col.col_foreign_key:
                _write(' REFERENCES {}', col.col_foreign_key.lower())
                if not col.col_nullable:
                    _write(' ON DELETE CASCADE')
                elif col.col_nullable:
                    _write(' ON DELETE SET NULL')
                _write(' ON UPDATE CASCADE')
            if col.col_primary_key:
                pk.append(col_name)
            if col_index:
                flat_append(indexes, col_index, col_name)
            if ci < len(table.cols) - 1:
                _writeline(',')
            else:
                _writeline()
        if pk:
            _writeline('\t, PRIMARY KEY ({})', ', '.join(pk))
        if uniques:
            for unique in uniques.values():
                _writeline('\t, UNIQUE ({}) DEFERRABLE', ', '.join(unique))
        _writeline(');')

        if table.comment:
            _writeline('COMMENT ON TABLE {} IS {};', table_name, _literal(table.comment))
        for col in table.cols:
            if col.col_comment:
                _writeline('COMMENT ON COLUMN {}.{} IS {};', table_name, col.col_name.lower(), _literal(col.col_comment))

        for ik, index in indexes.items():
            _writeline('CREATE INDEX {}_idx_{} ON {}({});', table_name, ik, table_name, ', '.join(index))


def execute(
    dsn: str,
    doc_path: str,
    sql_path: str,
    verbose: bool = False,
    drop_cascade: bool = False

) -> None:
    r''' 数据库连接字符串。
    '''
    tables = _load_tables_from_doc(doc_path, verbose)

    if sql_path:
        with open(sql_path, 'wt', encoding='utf-8') as f:
            _dump_sql(None, tables,drop_cascade, file=f)
    else:
        _dump_sql(None, tables,drop_cascade)
